package dao;

import bean.Book;
import bean.User;
import bean.Borrowed;
import jdbc.DbConn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

    //封装借阅信息的增删查改

public class BorrowedDao {
    private Connection conn;

    public BorrowedDao(){
        this.conn = DbConn.openDB();
    }
        /*
         * 通过用户编号查找借阅记录。
         * 从表tb_borrowed按用户编号user_id查找记录
         * 把记录封装成Borrow对象并返回
         * @param user 用户信息，至少包含用户编号，不能为空
         * @return List<Borrowed> 借阅信息List
         * */

    public List<Borrowed> queryBorrowedsByUser(User user){
        List<Borrowed> list = new ArrayList<Borrowed>();
        String sql = "SELECT\n" +
                "\ta.user_id,\n" +
                "\ta.book_id,\n" +
                "\ta.borrow_date,\n" +
                "\ta.return_date,\n" +
                "\tb.NAME,\n" +
                "\tc.NAME,\n" +
                "\tc.type,\n" +
                "\tc.author,\n" +
                "\tc.publisher,\n" +
                "\tc.price \n" +
                "FROM\n" +
                "\ttb_borrowed a,\n" +
                "\ttb_user b,\n" +
                "\ttb_book c \n" +
                "WHERE\n" +
                "\ta.user_id = b.id \n" +
                "\tAND a.book_id = c.id \n" +
                "\tAND a.user_id = ?;";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,user.getId());

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()){
                User user_t = new User(rs.getString(1), rs.getString(5),"","" );
                Book book_t = new Book(rs.getString(2), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9)
                ,rs.getDouble(10),"","","");

                list.add(new Borrowed(rs.getString(1),rs.getString(2),rs.getString(3)
                ,rs.getString(4),book_t,user_t));
            }
        }catch (SQLException ex){
            Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return list;
    }

        /*
        通过图书编号查找借阅记录
        从表tb_borrowed按图书编号book_id查找记录
        * 把记录封装成Borrow对象，包括用户信息，图书信息
         * @param book 图书信息，至少包含图书编号，不能为空
         * @return List<Borrowed> 借阅信息List
         */
        public List<Borrowed> queryBorrowedsByBook(Book book){
            List<Borrowed> list = new ArrayList<Borrowed>();
            String sql =
                    "SELECT\n" +
                            "\ta.user_id,\n" +
                            "\ta.book_id,\n" +
                            "\ta.borrow_date,\n" +
                            "\ta.return_date,\n" +
                            "\tb.NAME,\n" +
                            "\tc.NAME,\n" +
                            "\tc.type,\n" +
                            "\tc.author,\n" +
                            "\tc.publisher,\n" +
                            "\tc.price \n" +
                            "FROM\n" +
                            "\ttb_borrowed a,\n" +
                            "\ttb_user b,\n" +
                            "\ttb_book c \n" +
                            "WHERE\n" +
                            "\ta.user_id = b.id \n" +
                            "\tAND a.book_id = c.id \n" +
                            "\tAND a.book_id =?;";
            try {
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1,book.getId());

                ResultSet rs = pstmt.executeQuery();

                while (rs.next()){
                    User user_t = new User(rs.getString(1), rs.getString(5),"","" );
                    Book book_t = new Book(rs.getString(2), rs.getString(6),
                            rs.getString(7), rs.getString(8), rs.getString(9)
                            ,rs.getDouble(10),"","","");

                    list.add(new Borrowed(rs.getString(1),rs.getString(2),rs.getString(3)
                            ,rs.getString(4),book_t,user_t));
                }
            }catch (SQLException ex){
                Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
            }
            return list;
        }
            /*
        根据用户编号和图书编号查找借阅记录
        从表tb_borrowed按图书编号book_id和用户编号user_id查找记录
        * 把记录封装成Borrow对象，包括用户信息，图书信息
         * @param user 用户信息，至少包含用户编号，不能为空
          * @param book 图书信息，至少包含图书编号，不能为空
         * @return List<Borrowed> 借阅信息List
         */


    public List<Borrowed> queryBorrowedsByUser2Book(User user,Book book){
        List<Borrowed> list = new ArrayList<Borrowed>();
        String sql =
                "SELECT\n" +
                        "\ta.user_id,\n" +
                        "\ta.book_id,\n" +
                        "\ta.borrow_date,\n" +
                        "\ta.return_date,\n" +
                        "\tb.NAME,\n" +
                        "\tc.NAME,\n" +
                        "\tc.type,\n" +
                        "\tc.author,\n" +
                        "\tc.publisher,\n" +
                        "\tc.price \n" +
                        "FROM\n" +
                        "\ttb_borrowed a,\n" +
                        "\ttb_user b,\n" +
                        "\ttb_book c \n" +
                        "WHERE\n" +
                        "\ta.user_id = b.id \n" +
                        "\tAND a.book_id = c.id \n" +
                        "\tAND a.user_id =? \n" +
                        "\tAND a.book_id =?;";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,user.getId());
            pstmt.setString(2,book.getId());
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()){
                User user_t = new User(rs.getString(1), rs.getString(5),"","" );
                Book book_t = new Book(rs.getString(2), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9)
                        ,rs.getDouble(10),"","","");

                list.add(new Borrowed(rs.getString(1),rs.getString(2),rs.getString(3)
                        ,rs.getString(4),book_t,user_t));
            }
        }catch (SQLException ex){
            Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return list;
    }
      /*
        查找所有借阅记录
        从表tb_borrowed按图书编号book_id和用户编号user_id查找记录
        * 把记录封装成Borrow对象，包括用户信息，图书信息
         *
         * @return List<Borrowed> 借阅信息List
         */

    public List<Borrowed> queryBorroweds(){
        List<Borrowed> list = new ArrayList<Borrowed>();
        String sql = "SELECT\n" +
                "\ta.user_id,\n" +
                "\ta.book_id,\n" +
                "\ta.borrow_date,\n" +
                "\ta.return_date,\n" +
                "\tb.NAME,\n" +
                "\tc.NAME,\n" +
                "\tc.type,\n" +
                "\tc.author,\n" +
                "\tc.publisher,\n" +
                "\tc.price \n" +
                "FROM\n" +
                "\ttb_borrowed a,\n" +
                "\ttb_user b,\n" +
                "\ttb_book c \n" +
                "WHERE\n" +
                "\ta.user_id = b.id \n" +
                "\tAND a.book_id = c.id;";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()){
                User user_t = new User(rs.getString(1), rs.getString(5),"","" );
                Book book_t = new Book(rs.getString(2), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9)
                        ,rs.getDouble(10),"","","");

                list.add(new Borrowed(rs.getString(1),rs.getString(2),rs.getString(3)
                        ,rs.getString(4),book_t,user_t));
            }
        }catch (SQLException ex){
            Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return list;
    }


        /*
        添加一条借阅记录
        @param borrow
        @return boolean true成功 false 失败
         */
    public boolean addBorrowed(Borrowed borr){
        String sql = "insert into tb_borrowed(user_id,book_id,borrow_date,return_date)values(?,?,?,?)";

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, borr.getUser_id());
            pstmt.setString(2, borr.getBook_id());
            pstmt.setString(3, borr.getBorrow_date());
            pstmt.setString(4, borr.getReturn_date());


            int x = pstmt.executeUpdate();
            if(x>0){
                return true;
            }
        }catch (SQLException ex){
            Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return false;
    }


        /*
       修改一条借阅记录
       @param borr
       @return boolean true成功 false 失败
        */
        public boolean modBorrowed(Borrowed borr){
            String sql = "update tb_borrowed set borrow_date=?,return_date = ? where user_id=? and book_id=?";

            try{
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, borr.getBorrow_date());
                pstmt.setString(2, borr.getReturn_date());
                pstmt.setString(3, borr.getUser_id());
                pstmt.setString(4, borr.getBook_id());

                int x = pstmt.executeUpdate();
                if(x>0){
                    return true;
                }
            }catch (SQLException ex){
                Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
            }
            return false;
        }


        /*
       删除一条借阅信息
       @param borrow
       @return boolean true成功 false 失败
        */
        public boolean delBorrowed(Borrowed borr){
            String sql = "delete from tb_borrowed where user_id=? and book_id=?";
            try{
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, borr.getUser_id());
                pstmt.setString(2, borr.getBook_id());

                int x = pstmt.executeUpdate();
                if(x>0){
                    return true;
                }
            }catch (SQLException ex){
                Logger.getLogger(dao.BorrowedDao.class.getName()).log(Level.SEVERE,null,ex);
            }
            return false;
        }

        public static void main(String[] args) {
            BorrowedDao dao = new BorrowedDao();
            User user = new User("1","","","");
            Book book = new Book();
            book.setId("1");
            List<Borrowed> list = dao.queryBorrowedsByUser(user);

            for (Borrowed borr:list){
                System.out.println(borr.toString());
            }
        }
}
